﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using NangLuongTieuDiemDTO.DTO;

namespace NangLuongTieuDiemDAO.DAO
{
    public class ThietBiDienDAO
    {
        #region "[Insert]"
        /// <summary>
        /// Thêm 1 đối tượng ThietBiDien
        /// </summary>
        /// <param name="tbdDto">đối tượng ThietBiDien cần thêm</param>
        /// <returns></returns>
        public static int Insert(ThietBiDienDTO tbdDto)
        {
            int IDResult = -1;
            try
            {
                // Mo ket noi
                SqlConnection cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = "insert into ThietBiDien(TenThietBiDien, CongSuat, CongSuatHaoHut, HeSoDeBa, Xoa, GhiChu) OUTPUT INSERTED.Id values(@TenThietBiDien, @CongSuat, @CongSuatHaoHut, @HeSoDeBa, @Xoa, @GhiChu)";

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                cmd.Parameters.Add("@TenThietBiDien", SqlDbType.NText);
                cmd.Parameters.Add("@CongSuat", SqlDbType.Decimal);
                cmd.Parameters.Add("@CongSuatHaoHut", SqlDbType.Decimal);
                cmd.Parameters.Add("@HeSoDeBa", SqlDbType.Decimal);
                cmd.Parameters.Add("@Xoa", SqlDbType.Bit);
                cmd.Parameters.Add("@GhiChu", SqlDbType.NText);

                cmd.Parameters["@TenThietBiDien"].Value = tbdDto.TenThietBiDien;
                cmd.Parameters["@CongSuat"].Value = tbdDto.CongSuat;
                cmd.Parameters["@CongSuatHaoHut"].Value = tbdDto.CongSuatHaoHut;
                cmd.Parameters["@HeSoDeBa"].Value = tbdDto.HeSoDeBa;
                cmd.Parameters["@Xoa"].Value = tbdDto.Xoa;
                cmd.Parameters["@GhiChu"].Value = tbdDto.GhiChu;

                int k = cmd.ExecuteNonQuery();

                IDResult = k > 0 ? SelectMaxId() : -1;

                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Insert", ex);
            }

            return IDResult;
        }
        #endregion

        #region "[Update]"
        /// <summary>
        /// Cập nhật 1 đối tượng ThietBiDien
        /// </summary>
        /// <param name="tbdDto">đối tượng ThietBiDien cần cập nhật</param>
        /// <returns></returns>
        public static bool Update(ThietBiDienDTO tbdDto)
        {
            SqlConnection cn;
            bool isSuccess = false;
            try
            {

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = "Update ThietBiDien Set TenThietBiDien = @TenThietBiDien , CongSuat = @CongSuat , CongSuatHaoHut = @CongSuatHaoHut , HeSoDeBa = @HeSoDeBa , Xoa = @Xoa , GhiChu = @GhiChu where ID = @ID";

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                cmd.Parameters.Add("@TenThietBiDien", SqlDbType.NText);
                cmd.Parameters.Add("@CongSuat", SqlDbType.Decimal);
                cmd.Parameters.Add("@CongSuatHaoHut", SqlDbType.Decimal);
                cmd.Parameters.Add("@HeSoDeBa", SqlDbType.Decimal);
                cmd.Parameters.Add("@Xoa", SqlDbType.Bit);
                cmd.Parameters.Add("@GhiChu", SqlDbType.NText);
                cmd.Parameters.Add("@ID", SqlDbType.Int);

                cmd.Parameters["@TenThietBiDien"].Value = tbdDto.TenThietBiDien;
                cmd.Parameters["@CongSuat"].Value = tbdDto.CongSuat;
                cmd.Parameters["@CongSuatHaoHut"].Value = tbdDto.CongSuatHaoHut;
                cmd.Parameters["@HeSoDeBa"].Value = tbdDto.HeSoDeBa;
                cmd.Parameters["@Xoa"].Value = tbdDto.Xoa;
                cmd.Parameters["@GhiChu"].Value = tbdDto.GhiChu;
                cmd.Parameters["@ID"].Value = tbdDto.ID;

                int k = cmd.ExecuteNonQuery();

                // dong ket noi csdl
                cn.Close();
                isSuccess = k > 0 ? true : false;

            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Update", ex);
            }
            return isSuccess;
        }
        #endregion

        #region "[Delete]"
        /// <summary>
        /// xóa 1 đối tượng ThietBiDien
        /// </summary>
        /// <param name="ID">mã đối tượng ThietBiDien cần xóa</param>
        /// <returns></returns>
        public static bool Delete(int ID)
        {
            SqlConnection cn;
            bool isSuccess = false;
            try
            {
                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = " Update ThietBiDien Set Xoa = 1 where ID = @ID";


                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters["@ID"].Value = ID;


                cmd.ExecuteNonQuery();
                // dong ket noi csdl
                cn.Close();
                isSuccess= true;

            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Delete", ex);
            }
            return isSuccess;
        }
        #endregion

        #region "[Get all]"
        /// <summary>
        /// lấy danh sách các đối tượng ThietBiDien
        /// </summary>
        /// <returns></returns>
        public static List<ThietBiDienDTO> GetAll()
        {
            List<ThietBiDienDTO> ds_tbdDto = new List<ThietBiDienDTO>();
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                //string strSQL = " Select * From ThietBiDien";
                string strSQL = " Select * From ThietBiDien Where Xoa = 0";


                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                SqlDataReader dr = cmd.ExecuteReader();



                while (dr.Read() == true)
                {
                    //ThietBiDienDTO tbdDto = new ThietBiDienDTO();
                    //tbdDto.ID             = dr.GetInt32(0);
                    //tbdDto.TenThietBiDien = dr.GetString(1);
                    //tbdDto.CongSuat       = dr.GetFloat(2);
                    //tbdDto.CongSuatHaoHut = dr.GetFloat(3);
                    //tbdDto.HeSoDeBa       = dr.GetFloat(4);
                    //tbdDto.Xoa            = dr.GetBoolean(5);
                    //tbdDto.GhiChu         = dr.GetString(6);

                    ThietBiDienDTO tbdDto = new ThietBiDienDTO();
                    tbdDto.ID = dr.GetInt32(0);
                    tbdDto.TenThietBiDien = dr.GetString(1);
                    tbdDto.CongSuat = Convert.ToDouble(dr.GetDecimal(2));
                    tbdDto.CongSuatHaoHut = Convert.ToDouble(dr.GetDecimal(3));
                    tbdDto.HeSoDeBa = Convert.ToDouble(dr.GetDecimal(4));
                    tbdDto.Xoa = dr.GetBoolean(5);
                    tbdDto.GhiChu = dr.GetString(6);

                    ds_tbdDto.Add(tbdDto);
                }

                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("GetAll", ex);
            }
            return ds_tbdDto;
        }
        #endregion

        #region "[SelectByPrimaryKey]"
        /// <summary>
        /// lấy 1 đối tượng ThietBiDien
        /// </summary>
        /// <param name="ID">mã đối tượng ThietBiDien cần lấy</param>
        /// <returns></returns>
        public static ThietBiDienDTO SelectByPrimaryKey(int ID)
        {
            ThietBiDienDTO tbdDto = null;
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = " Select * From ThietBiDien where ID = @ID";


                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters["@ID"].Value = ID;

                SqlDataReader dr = cmd.ExecuteReader();
                tbdDto = new ThietBiDienDTO();

                while (dr.Read() == true)
                {
                    tbdDto.ID = dr.GetInt32(0);
                    tbdDto.TenThietBiDien = dr.GetString(1);
                    tbdDto.CongSuat = Convert.ToDouble(dr.GetDecimal(2));
                    tbdDto.CongSuatHaoHut = Convert.ToDouble(dr.GetDecimal(3));
                    tbdDto.HeSoDeBa = Convert.ToDouble(dr.GetDecimal(4));
                    tbdDto.Xoa = dr.GetBoolean(5);
                    tbdDto.GhiChu = dr.GetString(6);
                }

                // Dong ket noi csdl
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("SelectByPrimaryKey", ex);
            }
            return tbdDto;
        }

        public static int SelectMaxId()
        {
            SqlConnection cn;

            // Mo ket noi
            cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

            // Chuoi sql thao tac csdl

            string strSQL = " SELECT MAX(ID) FROM ThietBiDien";
            // Thuc thi chuoi sql
            SqlCommand cmd = new SqlCommand(strSQL, cn);

            // Dong ket noi csdl
            int maxId = (int)cmd.ExecuteScalar();
            cn.Close();
            return maxId;
        }
        #endregion

        #region "[Tìm kiếm thiết bị điện]"  //Doan Phu An- 1241302
        public static List<ThietBiDienDTO> TimKiemThietBiDien(string ChuoiTimKiem)
        {
            List<ThietBiDienDTO> ds_tbdDto = new List<ThietBiDienDTO>();
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl

                string strSQL = "select * from ThietBiDien where TenThietBiDien like N'%" + ChuoiTimKiem.ToString() + "%'" + " or CongSuat like '" + ChuoiTimKiem.ToString() + "'" + " or CongSuatHaoHut like '" + ChuoiTimKiem.ToString() + "'" + " or HeSoDeBa like '" + ChuoiTimKiem.ToString() + "'" + " or GhiChu like N'%" + ChuoiTimKiem.ToString() + "%'";
                
                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

               SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read() == true)
                {
                    ThietBiDienDTO tbdDto = new ThietBiDienDTO();
                    tbdDto.ID = dr.GetInt32(0);
                    tbdDto.TenThietBiDien = dr.GetString(1);
                    tbdDto.CongSuat = Convert.ToDouble(dr.GetDecimal(2));
                    tbdDto.CongSuatHaoHut = Convert.ToDouble(dr.GetDecimal(3));
                    tbdDto.HeSoDeBa = Convert.ToDouble(dr.GetDecimal(4));
                    tbdDto.Xoa = dr.GetBoolean(5);
                    tbdDto.GhiChu = dr.GetString(6);
                    ds_tbdDto.Add(tbdDto);
                }

                // Dong ket noi csdl
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("TimKiemThietBiDien", ex);
            }
            return ds_tbdDto;
        }
        #endregion

        #region "[Hàm insert lỗi hệ thống]"
        public static void InSert_LoiHeThong(string TenHam, Exception ex)
        {
            LoiHeThongDTO lhtDto = new LoiHeThongDTO();
            lhtDto.TenLoi = ex.Message.ToString();
            lhtDto.NgayTao = DateTime.Now;
            lhtDto.TenHamBiLoi = "ThietBiDienDAO_" + TenHam;
            lhtDto.SuaLoi = false;
            lhtDto.GhiChu = "";
            LoiHeThongDAO.Insert(lhtDto);

        }
        #endregion
    }
}
